How to Get the First Row per Group

The function row_number() returns the position of a record in an ordered group. To obtain the first record in a group of records, you can use the with clause to create a common table expression including row_number() to obtain the position of each row in the group. Later with an outer query you can filter rows by position.

with rows_and_position as 
  ( 
    select emp_id,
    last_name, 
    salary,
    dep_id,
    row_number() over (partition by dep_id order by salary desc) as position
    from employee
       )
select dep_id, last_name, salary
from  rows_and_position
where position = 1

The approach presented here can be used to obtain records in any position in a group.

IN THIS PAGE